package com.hfjyz.registration.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.util.StringUtils;

import com.hfjyz.registration.command.AnnouncementListCommand;
import com.hfjyz.registration.command.ExamResultCountListCommand;
import com.hfjyz.registration.model.Exam;
import com.hfjyz.registration.model.Master;
import com.hfjyz.registration.object.ExamResultInfoBean;
import com.hfjyz.registration.util.MasterUtil;

public class ExamDaoImpl extends BaseDaoImpl<Exam, Integer> implements ExamDao {

	protected ExamDaoImpl() {
		super(Exam.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Exam> search(final AnnouncementListCommand cmd) {
		return (List<Exam>) getHibernateTemplate().execute(
				new HibernateCallback() {

					@Override
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						Criteria rowCountCriteria = createCriteria(cmd, session);
						rowCountCriteria.setProjection(Projections.rowCount());
						Long rowCount = (Long) rowCountCriteria.uniqueResult();

						if (rowCount != null && rowCount > 0) {
							cmd.setRowCount(rowCount.intValue());
							Criteria criteria = createCriteria(cmd, session);
							criteria.setFirstResult(cmd.getRowStart());
							criteria.setMaxResults(cmd.getRowsPerPage());
							return (List<Exam>) criteria.list();
						}
						cmd.setRowCount(0);
						return new ArrayList<Exam>(0);
					}

					private Criteria createCriteria(
							final AnnouncementListCommand cmd, Session session) {
						Criteria criteria = session.createCriteria(Exam.class);
						if (cmd.getEid() != null && cmd.getEid() != -1) {
							criteria.add(Restrictions.eq("id", cmd.getEid()));
						}
						if (StringUtils.hasLength(cmd.getName())) {
							criteria.add(Restrictions.like("name",
									cmd.getName(), MatchMode.ANYWHERE));
						}

						Calendar cl = Calendar.getInstance();
						// cl.set
						Date now = cl.getTime();
						cl.add(Calendar.DAY_OF_YEAR, -30);
						Date future = cl.getTime();
						if (cmd.getStatus() != null && cmd.getStatus() > -1) {
							Master m = MasterUtil.getInstance().getMaster(
									cmd.getStatus());
							Criterion score = Restrictions.or(
									Restrictions.isNull("scorePublishDate"),
									Restrictions.ge("scorePublishDate", future));
							// 未发布
							if ("01".equals(m.getCode())) {
								criteria.add(Restrictions
										.ge("publishDate", now));
							}
							// 发布中
							if ("02".equals(m.getCode())) {
								criteria.add(Restrictions
										.le("publishDate", now));
								criteria.add(score);
							}
							// 接受报名中
							if ("03".equals(m.getCode())) {
								criteria.add(Restrictions.le("applyBeginDate",
										now));
								criteria.add(Restrictions.ge("applyDeadDate",
										now));
								criteria.add(score);
							}
							// 准考证打印中
							if ("04".equals(m.getCode())) {
								criteria.add(Restrictions.le(
										"admissionPrintStart", now));
								criteria.add(Restrictions.ge(
										"admissionPrintEnd", now));
								criteria.add(score);
							}
							// 考试结束，成绩录入中
							if ("05".equals(m.getCode())) {
								criteria.add(Restrictions.le("examDate", now));
								criteria.add(Restrictions.ge(
										"scorePublishDate", now));
								criteria.add(score);
							}
							// 成绩发布中
							if ("06".equals(m.getCode())) {
								criteria.add(Restrictions.le(
										"scorePublishDate", now));
								criteria.add(score);
							}
							// 未结束
							if ("07".equals(m.getCode())) {
								criteria.add(score);
							}
							// 已结束
							if ("99".equals(m.getCode())) {
								criteria.add(Restrictions.le(
										"scorePublishDate", future));
							}

						}
						return criteria;
					}

				});
	}

	@SuppressWarnings("rawtypes")
	@Override
	public List<ExamResultInfoBean> initExamResult(
			ExamResultCountListCommand cmd) {
		final List<Double> paramList = new ArrayList<Double>();
		paramList.add(60.0);
		paramList.add(60.0);
		paramList.add(70.0);
		paramList.add(70.0);
		paramList.add(80.0);
		paramList.add(80.0);
		paramList.add(90.0);
		paramList.add(90.0);
		paramList.add(100.0);
		String str = "select exam.name, exam.examTime, exam.examDate, count(admission.score) as scoreCount,"
				+ " '0~60' as scoreSection from Exam as exam, Admission as admission "
				+ "where  admission.examId = exam.id and admission.score < ? GROUP BY exam.id UNION "
				+

				"select exam.name, exam.examTime, exam.examDate,count(admission.score) as scoreCount,"
				+ " '60~70' as scoreSection from Exam as exam, Admission as admission "
				+ "where  admission.examId = exam.id and admission.score >= ? "
				+ "and admission.score < ? GROUP BY exam.id UNION "
				+

				"select exam.name, exam.examTime, exam.examDate, count(admission.score) as scoreCount,"
				+ " '70~80' as scoreSection from Exam as exam, Admission as admission "
				+ "where  admission.examId = exam.id and admission.score >= ? "
				+ "and admission.score < ? GROUP BY exam.id UNION "
				+

				"select exam.name, exam.examTime, exam.examDate, count(admission.score) as scoreCount,"
				+ " '80~90' as scoreSection from Exam as exam, Admission as admission "
				+ "where  admission.examId = exam.id and admission.score >= ? "
				+ "and admission.score < ? GROUP BY exam.id UNION "
				+

				"select exam.name, exam.examTime, exam.examDate, count(admission.score) as scoreCount,"
				+ " '90~100' as scoreSection from Exam as exam, Admission as admission "
				+ "where  admission.examId = exam.id and admission.score >= ? "
				+ "and admission.score <= ? GROUP BY exam.id ";
		final String sql = str;
		List listResult = hibernateTemplate
				.executeFind(new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						SQLQuery query = session.createSQLQuery(sql);
						for (int i = 0; i < paramList.size(); i++) {
							query.setDouble(i, paramList.get(i));
						}
						return query.list();
					}
				});
		List<ExamResultInfoBean> beanList = new ArrayList<ExamResultInfoBean>();
		for (Object o : listResult) {
			ExamResultInfoBean bean = new ExamResultInfoBean();
			Object[] tmp = (Object[]) o;
			bean.setName((String) tmp[0]);
			bean.setExamTime((String) tmp[1]);
			bean.setExamDate((Date) tmp[2]);
			bean.setScoreCount(String.valueOf(tmp[3]));
			bean.setScoreSection(String.valueOf(tmp[4]));
			beanList.add(bean);
		}

		return beanList;
	}

}
